﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMTC.Model.Model;
using System.Data.Common;
using IMPlant.Core.Data.Unity;
using System.Data;
using SMTC.Model.Domain;

namespace SMTC.Dao.Plan
{
    public class PlanQueryDao : BaseDao
    {
        private const string SQL_SELECT_PLAN = @"WITH unit AS
                                                (
                                                  SELECT  ProductCode,SUM(UnitCount) UnitCount,max(isnull(isTemp,0)) isTemp FROM dbo.SMT_ProductBaseInfo GROUP BY ProductCode
                                                )
                                                SELECT h.PlanID Id,h.xSVMaterial MaterialCode,h.BusinessUnit bu,h.PlanVersion
                                                      ,d.m1,d.m2,d.m3,d.m4,d.m5,d.m6,d.m7,d.m8,d.m9,d.m10,d.m11,d.m12,isnull(unit.UnitCount,0) UnitCount,isnull(unit.isTemp,0) isTemp
                                                 FROM dbo.SMT_PlanHead h LEFT JOIN 
                                                (SELECT PlanID ID,MAX(Planyear) [year],MAX(xsvmateria) MaterialCode
                                                       ,SUM([1]) 'm1',SUM([2]) 'm2',SUM([3]) 'm3',SUM([4]) 'm4'
                                                       ,SUM([5]) 'm5',SUM([6]) 'm6',SUM([7]) 'm7',SUM([8]) 'm8'
                                                       ,SUM([9]) 'm9',SUM([10]) 'm10',SUM([11]) 'm11',SUM([12]) 'm12'
                                                FROM dbo.SMT_PlanDetail 
                                                PIVOT ( SUM(planmount) FOR planmonth IN (
                                                 [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
                                                )
                                                  ) AS pvt
                                                GROUP BY PlanID
                                                ) d ON h.PlanID = d.ID
                                                LEFT JOIN unit ON h.xSVMaterial = unit.ProductCode
                                                WHERE h.xSVMaterial LIKE @xSVMaterial
                                                AND h.BusinessUnit LIKE @BusinessUnit
                                                AND h.PlanVersion = @PlanVersion
                                                ";

        public List<ImportPlanDomain> GetPlanInfo(string xSVMaterial, string BusinessUnit, string PlanVerion, int haveUnitFlag = 0)
        {
            string Sql = "";
            if (haveUnitFlag == 0)
            {
                Sql = SQL_SELECT_PLAN;
            }
            else if (haveUnitFlag == 1)
            {
                Sql = SQL_SELECT_PLAN + " AND ISNULL(isTemp,0) = 1";
            }
            else if (haveUnitFlag == 2)
            {
                Sql = SQL_SELECT_PLAN + " AND ISNULL(unit.UnitCount,0) = 0"; ;
            }
            else
            {
                Sql = SQL_SELECT_PLAN;
            }

            DbParameter[] parameters = 
            {
                DBparameterBuild.Build("@xSVMaterial",xSVMaterial)
                ,DBparameterBuild.Build("@BusinessUnit",BusinessUnit)
                ,DBparameterBuild.Build("@PlanVersion",PlanVerion)
            };
            return DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, Sql, parameters);
        }


    }
}
